package com.icim.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.icim.entity.Ret_prd_jge;

@Repository
public class RetPrdJgeDao {
    @Resource
    private JdbcTemplate icimJdbcTemplate;

    
    private Ret_prd_jge setEntityByRs(ResultSet rs) throws SQLException {
		Ret_prd_jge retPrdJge = new Ret_prd_jge();
        retPrdJge.setPrd_seq_id_fk(rs.getString("PRD_SEQ_ID_FK"));
        retPrdJge.setRsn_cate(rs.getString("RSN_CATE"));
        retPrdJge.setDef_code1(rs.getString("DEF_CODE1"));
        retPrdJge.setDef_code1_lc(rs.getString("DEF_CODE1_LC"));
        retPrdJge.setJudge_usr(rs.getString("JUDGE_USR"));
        retPrdJge.setJudge_timestamp(rs.getTimestamp("JUDGE_TIMESTAMP"));
        retPrdJge.setOpe_id_fk(rs.getString("OPE_ID_FK"));
        retPrdJge.setOpe_ver_fk(rs.getString("OPE_VER_FK"));
        retPrdJge.setDef_code1_cnt(rs.getInt("DEF_CODE1_CNT"));
        retPrdJge.setDef_code1_sd(rs.getString("DEF_CODE1_SD"));
        return retPrdJge;
	}
    public List<Ret_prd_jge> getDefectRecorders(Timestamp beginTimeStamp, Timestamp endTimeStamp, String cusId) {
        StringBuffer sqlBuffer = new StringBuffer("SELECT * FROM RET_PRD_JGE WHERE");
        sqlBuffer.append(" JGE_TIMESTAMP BETWEEN ? AND ? ");
        sqlBuffer.append(" AND PRD_SEQ_ID_FK IN (");
        sqlBuffer.append(" SELECT PRD_SEQ_ID FROM RET_PRD_INFO WHERE WO_ID_FK IN ( ");
        sqlBuffer.append(" SELECT WO_ID FROM RET_WO WHERE WO_TYP = 'W' AND CUS_ID_FK = ? ))");
        return icimJdbcTemplate.query(sqlBuffer.toString(), new Object[] { beginTimeStamp, endTimeStamp, cusId },
                new RowMapper<Ret_prd_jge>() {

                    @Override
                    public Ret_prd_jge mapRow(ResultSet rs, int rowNum) throws SQLException {
                    	 return setEntityByRs(rs);
                    }
                });

    }
    
    public List<Ret_prd_jge> getDefProductByBoxId(String boxId)
    {
    	String sql = "select * from ret_prd_jge where box_id_fk = '"+boxId+"'";
    	return icimJdbcTemplate.query(sql, new RowMapper<Ret_prd_jge>() {

            @Override
            public Ret_prd_jge mapRow(ResultSet rs, int rowNum) throws SQLException {
                return setEntityByRs(rs);
            }
        });
    }
    
    public List<Ret_prd_jge> getDefProductByPID(String productId)
    {
    	String sql = "select * from ret_prd_jge where prd_seq_id_fk = '"+productId+"'";
    	return icimJdbcTemplate.query(sql, new RowMapper<Ret_prd_jge>() {

            @Override
            public Ret_prd_jge mapRow(ResultSet rs, int rowNum) throws SQLException {
                return setEntityByRs(rs);
            }
        });
    }

    public List<Ret_prd_jge> getDefectCordersBySheet(Collection<String> sheetIdCollection) {
        StringBuffer sql = new StringBuffer(
                "SELECT * FROM RET_PRD_JGE WHERE LST_JUDGE_FLG='Y' AND PROC_ID<>'JBYZ' AND PRD_SEQ_ID_FK IN (");
        int cnt = sheetIdCollection.size();
        String[] sheetIds = new String[cnt];
        sheetIdCollection.toArray(sheetIds);
        for (int i = 0; i < cnt; i++) {
            String sheetId = sheetIds[i];
            sql.append("'").append(sheetId).append("'");
            if (i < cnt - 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        return icimJdbcTemplate.query(sql.toString(), new RowMapper<Ret_prd_jge>() {

            @Override
            public Ret_prd_jge mapRow(ResultSet rs, int rowNum) throws SQLException {
                return setEntityByRs(rs);
            }

        });
    }

    public Map<String, List<Ret_prd_jge>> getDefectMapBySheets(Collection<String> sheetIdCollection) {
        Map<String, List<Ret_prd_jge>> retPrdJgeMap = new HashMap<String, List<Ret_prd_jge>>();
        List<Ret_prd_jge> retPrdJgeList = getDefectCordersBySheet(sheetIdCollection);
        
        for (Ret_prd_jge ret_prd_jge : retPrdJgeList) {
            String prdSeqId = ret_prd_jge.getPrd_seq_id_fk();
            List<Ret_prd_jge> defectList = retPrdJgeMap.get(prdSeqId);
            defectList = (defectList == null) ? (new ArrayList<Ret_prd_jge>()) : defectList;
            defectList.add(ret_prd_jge);
            retPrdJgeMap.put(prdSeqId, defectList);
        }
        return retPrdJgeMap;

    }
}
